package cn.explink.dop.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;

import cn.explink.dop.entity.vo.RestaurantVO;
import cn.explink.dop.entity.vo.StorefrontVO;
import cn.explink.eap.core.common.dao.impl.GenericBaseCommonDao;

/**
 * 餐厅管理dao层
 *
 * @author firefly 2015-2-27 10:05
 *
 */
@Repository("storefrontDao")
public class StorefrontDao extends GenericBaseCommonDao<StorefrontVO, String> {
	/**
	 * 添加数据
	 *
	 * @param res
	 *            添加对象
	 * @return 添加成功之后对象
	 */
	// public StorefrontVO add(final StorefrontVO sto) {
	// try {
	// final String sql =
	// "insert into bd_storefront (sto_name,sto_address,sto_coordinate,sto_starttime,sto_endtime,restaurant_id) "
	// + "values(?,?,?,?,?,?)";
	// KeyHolder keyHolder = new GeneratedKeyHolder();
	// this.getJdbcTemplate().update(new PreparedStatementCreator() {
	// @Override
	// public PreparedStatement createPreparedStatement(Connection connection)
	// throws SQLException {
	// PreparedStatement ps = connection.prepareStatement(sql, new String[] {
	// "id" });
	// ps.setString(1, sto.getStoName());
	// ps.setString(2, sto.getStoAddress());
	// ps.setString(3, sto.getStoCoordinate());
	// ps.setString(4, sto.getStoStarttime());
	// ps.setString(5, sto.getStoEndtime());
	// ps.setLong(6, sto.getRestaurantId());
	// return ps;
	// }
	// }, keyHolder);
	// sto.setId(keyHolder.getKey().toString());
	// return sto;
	// } catch (Exception e) {
	// return new StorefrontVO();
	// }
	// }

	/**
	 * 修改基本信息
	 *
	 * @param baseInfo
	 *            基本信息对象
	 */
	public void modify(final StorefrontVO sto) {
		StringBuffer sql = new StringBuffer("update bd_storefront set ");
		if (StringUtils.isNotBlank(sto.getStoName())) {
			sql.append("sto_name='").append(sto.getStoName()).append("',");
		}
		if (StringUtils.isNotBlank(sto.getStoAddress())) {
			sql.append("sto_address='").append(sto.getStoAddress()).append("',");
		}
		if (StringUtils.isNotBlank(sto.getStoCoordinate())) {
			sql.append("sto_coordinate='").append(sto.getStoCoordinate()).append("',");
		}
		if (StringUtils.isNotBlank(sto.getStoStarttime())) {
			sql.append("sto_starttime='").append(sto.getStoStarttime()).append("',");
		}
		if (StringUtils.isNotBlank(sto.getStoEndtime())) {
			sql.append("sto_endtime='").append(sto.getStoEndtime()).append("',");
		}
		if ((null != sto.getRestaurant()) && StringUtils.isNotBlank(sto.getRestaurant().getId())) {
			sql.append("restaurant_id='").append(sto.getRestaurant().getId()).append("'");
		}
		sql.append(" ").append("where id='").append(sto.getId()).append("'");
		super.getJdbcTemplate().update(sql.toString());
	}

	/**
	 * 按照实体查询，无分页
	 *
	 * @param res
	 *            查询实体
	 * @return 查询列表
	 */
	public List<StorefrontVO> finds(final StorefrontVO sto) {
		StringBuffer allFromSql = new StringBuffer("from StorefrontVO where 1=1 ");
		if (sto.getStoStatus() == null) {
			allFromSql.append(" and stoStatus<>3");
		} else {
			allFromSql.append("  and stoStatus=" + sto.getStoStatus());
		}
		// StringBuffer whereSql = this.getWhereSql(sto);

		@SuppressWarnings("unchecked")
		List<StorefrontVO> resList = this.getSession().createQuery(allFromSql.toString()).list();
		return resList;
	}

	public StorefrontVO queryById(String storefrontId) {
		Query query = this.getSession().createQuery(" from StorefrontVO where id= :storefrontId");
		query.setString("storefrontId", storefrontId);
		return (StorefrontVO) query.uniqueResult();
	}

	/**
	 * 按照实体获取分页数据
	 *
	 * @param baseInfo
	 *            查询实体
	 * @param pageNum
	 *            当前页
	 * @param pageSize
	 *            当前页记录条数
	 * @return 查询结果集
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> findByPage(final StorefrontVO sto, final int pageNum, final int pageSize) {
		StringBuffer allFromSql = new StringBuffer("from StorefrontVO where 1=1 ");
		StringBuffer countFromSql = new StringBuffer("select count(id) from StorefrontVO where 1=1 ");
		List<Object> param = new ArrayList<Object>();
		StringBuffer whereSql = new StringBuffer();
		if (StringUtils.isNotBlank(sto.getStoName())) {
			whereSql.append(" and sto_name like ? ");
			param.add("%" + sto.getStoName() + "%");
		}
		if (StringUtils.isNotBlank(sto.getStoAddress())) {
			whereSql.append(" and sto_address like ? ");
			param.add("%" + sto.getStoAddress() + "%");
		}
		if ((null != sto.getRestaurant()) && StringUtils.isNotBlank(sto.getRestaurant().getId())) {
			whereSql.append(" and restaurant_id=? ");
			param.add(sto.getRestaurant().getId());
		}
		whereSql.append(" and sto_status<>3 ");
		Map<String, Object> map = new HashMap<String, Object>();

		Query queryCount = this.getSession().createQuery(countFromSql.append(whereSql).toString());
		Query query = this.getSession().createQuery(allFromSql.append(whereSql).toString());
		for (int i = 0; i < param.size(); i++) {
			queryCount.setParameter(i, param.get(i));
			query.setParameter(i, param.get(i));
		}
		Integer count = ((Long) queryCount.uniqueResult()).intValue();
		map.put("count", count);
		query.setFirstResult((pageNum - 1) * pageSize);
		query.setMaxResults(pageSize);
		List<StorefrontVO> resList = query.list();
		map.put("list", resList);
		return map;
	}

	private StringBuffer getWhereSql(final StorefrontVO sto) {
		StringBuffer sql = new StringBuffer("");
		if (null != sto) {
			sql.append("where");
		}
		if (StringUtils.isNotBlank(sto.getStoName())) {
			sql.append(" and sto_name='").append(sto.getStoName()).append("'");
		}
		if (StringUtils.isNotBlank(sto.getStoAddress())) {
			sql.append(" and sto_address='").append(sto.getStoAddress()).append("'");
		}
		if (StringUtils.isNotBlank(sto.getStoCoordinate())) {
			sql.append(" and sto_coordinate='").append(sto.getStoCoordinate()).append("'");
		}
		if (StringUtils.isNotBlank(sto.getStoStarttime())) {
			sql.append(" and sto_starttime>='").append(sto.getStoStarttime()).append("'");
		}
		if (StringUtils.isNotBlank(sto.getStoEndtime())) {
			sql.append(" and sto_endtime<='").append(sto.getStoEndtime()).append("'");
		}
		if ((null != sto.getRestaurant()) && StringUtils.isNotBlank(sto.getRestaurant().getId())) {
			sql.append(" and restaurant_id=").append(sto.getRestaurant().getId());
		}
		sql.append(" and sto_status<>3 ");
		return sql;
	}

	/**
	 * 删除选中对象
	 *
	 * @param baseInfoList
	 *            参数集合
	 */
	public int delete(final List<StorefrontVO> resList) {
		StringBuffer sql = new StringBuffer("update  bd_storefront set sto_status=3");
		TreeSet<String> set = new TreeSet<String>();
		for (StorefrontVO s : resList) {
			set.add("'" + s.getId() + "'");
		}
		sql.append(" ").append("where id in (").append(StringUtils.join(set.toArray(), ",")).append(")");
		return super.getJdbcTemplate().update(sql.toString());
	}

	private String processWhere(String sqlStr) {
		if (sqlStr.contains("where and")) {
			sqlStr = sqlStr.replace("where and", "where");
		} else if (sqlStr.contains("where")) {
			sqlStr = sqlStr.replace("where", "");
		}
		return sqlStr;
	}

	/**
	 *
	 * @Title: openSto
	 * @Description: 启用店面
	 * @author 孟凡岭
	 * @param @param idsArr
	 * @return void 返回类型
	 * @throws
	 */
	public void openSto(final String[] idsArr) {
		// TODO Auto-generated method stub
		String sql = "update bd_storefront set sto_status=1 where id=?";
		this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, idsArr[i]);
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return idsArr.length;
			}
		});
	}

	/**
	 *
	 * @Title: queryOrderByIds
	 * @Description: 通过id或多个id查询出未完成的订单
	 * @author 孟凡岭
	 * @param @param idsArr
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> queryOrderByIds(String[] idsArr) {
		// TODO Auto-generated method stub
		Set<String> set = new HashSet<String>();
		for (int i = 0; i < idsArr.length; i++) {
			set.add("'" + idsArr[i] + "'");
		}
		String ids = StringUtils.join(set.toArray(), ",");
		String sql = "select id from or_order where (status<>5 and status<>6) and storefront_id in (" + ids + ")";
		return this.getJdbcTemplate().queryForList(sql);
	}

	/**
	 * 重载方法
	 *
	 * @Title: stopSto
	 * @Description: 停用店面
	 * @author 孟凡岭
	 * @param @param idsArr
	 * @return void 返回类型
	 * @throws
	 */
	public void stopSto(final String[] idsArr) {
		// TODO Auto-generated method stub
		String sql = "update bd_storefront set sto_status=2 where id=?";
		this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, idsArr[i]);
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return idsArr.length;
			}
		});
	}

	/**
	 * 重载方法
	 *
	 * @Title: queryOrderByIds
	 * @Description: 通过id或多个id查询出未完成的订单
	 * @author 孟凡岭
	 * @param @param stoList
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> queryOrderByIds(List<StorefrontVO> stoList) {
		// TODO Auto-generated method stub
		List<String> list = new ArrayList<String>();
		for (int i = 0; i < stoList.size(); i++) {
			StorefrontVO vo = stoList.get(i);
			list.add("'" + vo.getId() + "'");
		}
		String ids = StringUtils.join(list.toArray(), ",");
		String sql = "select id from or_order where (status<>5 and status<>6 and status<>7 and status<>10 ) and storefront_id in (" + ids + ")";
		return this.getJdbcTemplate().queryForList(sql);
	}

	/**
	 *
	 * @Title: findByRestaurantId
	 * @Description: 通过餐厅ID查询出店面
	 * @author 孟凡岭
	 * @param @param res
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> findByRestaurantId(List<RestaurantVO> res) {
		// TODO Auto-generated method stub
		TreeSet<String> set = new TreeSet<String>();
		for (RestaurantVO r : res) {
			set.add("'" + r.getId() + "'");
		}
		String sql = "select id from bd_storefront where restaurant_id in (" + StringUtils.join(set.toArray(), ",") + ")";

		return this.getJdbcTemplate().queryForList(sql);
	}

	/**
	 *
	 * @Title: stopByRestaurantId
	 * @Description: 停用店面
	 * @author 孟凡岭
	 * @param @param strings
	 * @return void 返回类型
	 * @throws
	 */
	public void stopByRestaurantId(String restaurantIds) {
		// TODO Auto-generated method stub
		String sql = "update bd_storefront set sto_status=2 where restaurant_id in(" + restaurantIds + ")";
		this.getJdbcTemplate().update(sql);
	}

	/**
	 *
	 * @Title: openRows
	 * @Description: 启用店面
	 * @author 孟凡岭
	 * @param @param split
	 * @return void 返回类型
	 * @throws
	 */
	public void openRows(String restaurantIds) {
		// TODO Auto-generated method stub
		String sql = "update bd_storefront set sto_status=1 where restaurant_id in(" + restaurantIds + ")";
		this.getJdbcTemplate().update(sql);
	}

	/**
	 *
	 * @Title: queryRestaurantId
	 * @Description: 查询店面的所属餐厅ID
	 * @author 孟凡岭
	 * @param @param idsArr
	 * @param @return
	 * @return List<Object> 返回类型
	 * @throws
	 */
	public List<Object> queryRestaurantId(String[] idsArr) {
		// TODO Auto-generated method stub
		String sql = "select restaurant_id from bd_storefront where id in(";
		StringBuffer ids = new StringBuffer();
		for (int i = 0; i < idsArr.length; i++) {
			ids.append(",'" + idsArr[i] + "'");
		}
		sql = sql + ids.substring(1) + ")";
		SQLQuery query = this.getSession().createSQLQuery(sql);
		List<Object> list = query.list();
		return list;
	}

	/**
	 *
	 * @Title: updateParentRestaurant
	 * @Description: 启用餐厅
	 * @author 孟凡岭
	 * @param @param ids
	 * @return void 返回类型
	 * @throws
	 */
	public void updateParentRestaurant(String ids, int status) {
		// TODO Auto-generated method stub
		final String[] arr = ids.split(",");
		String sql = " update bd_restaurant set  res_enable=" + status + " where id=?";
		this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, arr[i]);
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return arr.length;
			}
		});
	}

	/**
	 *
	 * @Title: queryParentIds
	 * @Description: 返回没有下属已经没有店面的餐厅Id集合
	 * @author 孟凡岭
	 * @param @param uniquerList
	 * @param @return
	 * @return List<String> 返回类型
	 * @throws
	 */
	public List<String> queryParentIds(List<Object> uniquerList) {
		// TODO Auto-generated method stub
		String sql = "select id from bd_storefront where sto_status=1 and  restaurant_id=?";
		List<String> ids = new ArrayList<String>();
		for (int i = 0; i < uniquerList.size(); i++) {
			List<Map<String, Object>> result = this.getJdbcTemplate().queryForList(sql, uniquerList.get(i));
			if (result.size() == 0) {
				ids.add(uniquerList.get(i).toString());
			}
		}
		return ids;
	}

	/**
	 *
	 * @Title: updateParentRestaurant
	 * @Description: 停用餐厅
	 * @author 孟凡岭
	 * @param @param parentIds
	 * @param @param status
	 * @return void 返回类型
	 * @throws
	 */
	public void updateParentRestaurant(final List<String> parentIds, int status) {
		// TODO Auto-generated method stub
		String sql = " update bd_restaurant set  res_enable=" + status + " where id=?";
		this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, parentIds.get(i));
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return parentIds.size();
			}
		});
	}

	/**
	 *
	 * @Title: queryStoreIds
	 * @Description: 通过餐厅ID查询出下属店面ID
	 * @author 孟凡岭
	 * @param @param ids
	 * @param @return
	 * @return List<Object> 返回类型
	 * @throws
	 */
	public List<String> queryStoreIds(String restaurantId) {
		// TODO Auto-generated method stub
		String sql = "select id from bd_storefront store where store.restaurant_id in (" + restaurantId + ")";

		return this.getJdbcTemplate().queryForList(sql, String.class);
	}
}
